﻿using System;
using System.Collections;
using System.Data.SQLite;
using System.Text;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using MediaPortal.Configuration;
using MediaPortal.GUI.Library;
using MPMovieCatalog.Domain;
using MPMovieCatalog.Exception;
using log4net;

namespace MPMovieCatalog.Service
{
    public class MovieService : MarshalByRefObject, IMovieService
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(MovieService));

        private const string MOVIE_SHORT_INFO_COMMAND = "select idMovie, strTitle from movieinfo mi";

        private const string ALL_MOVIE_INFO_COMMAND = "select mi.*, a.strActor as strDirector from movieinfo mi left join actors a on a.idActor = mi.idDirector";

        private const string MOVIE_INFO_COMMAND = "select mi.*, a.strActor as strDirector from movieinfo mi left join actors a on a.idActor = mi.idDirector where mi.idMovie in ({0})";

        private const string MOVIE_ACTORS_COMMAND = "select a.* from movieinfo mi, actors a, actorlinkmovie alm where a.idActor = alm.idActor and alm.idMovie = mi.idMovie and alm.idActor != mi.idDirector and mi.idMovie = @movieId order by a.strActor";

        private const string MOVIE_GENRES_COMMAND = "select g.* from genre g, genrelinkmovie glm where g.idGenre = glm.idGenre and glm.idMovie = @movieId order by g.strGenre";

        private const string MOVIE_FILES_COMMAND = "select f.*, p.strPath from files f, path p where p.idPath = f.idPath and f.idMovie = @movieId order by f.strFilename";

        private const string INSERT_MOVIE_COMMAND = "insert into movie(idPath, hasSubtitles, discId) values (@pathId, @hasSubtitles, @discId); select last_insert_rowid() as [id]";

        private const string INSERT_MOVIE_INFO_COMMAND = "insert into movieinfo(idMovie, strTitle, strTagLine, idDirector, strCredits, iYear, runtime, strPlot, strPlotOutLine, fRating, IMDBID, strVotes, mpaa, iswatched, strCast, strGenre) values (@movieId, @title, @tagline, @directorId, @writer, @year, @runtime, @description, @description, @imdbRating, @imdbNumber, @imdbVotes, @mpaaRating, @isWatched, @actors, @genres)";

        private const string UPDATE_MOVIE_INFO_COMMAND = "update movieinfo set strTitle = @title, strTagLine = @tagline, idDirector = @directorId, strCredits = @writer, iYear = @year, runtime = @runtime, strPlot = @description, strPlotOutLine = @description, fRating = @imdbRating, IMDBID = @imdbNumber, strVotes = @imdbVotes, mpaa = @mpaaRating, iswatched = @isWatched, strCast = @actors, strGenre = @genres where idMovie = @movieId";

        private const string DELETE_MOVIE_INFO_COMMAND = "delete from movieinfo where idMovie = @movieId";

        private const string AVAIL_ACTORS_COMMAND = "select a.* from actors a where a.idActor not in (select alm.idActor from movieinfo mi, actorlinkmovie alm where alm.idMovie = mi.idMovie and alm.idActor != mi.idDirector and mi.idMovie = @movieId)";

        private const string AVAIL_GENRES_COMMAND = "select g.* from genre g where g.idGenre not in (select idGenre from genrelinkmovie glm where glm.idMovie = @movieId) order by g.strGenre";

        private const string DELETE_MOVIE_ACTORS_COMMAND = "delete from actorlinkmovie where idMovie = @movieId";

        private const string DELETE_MOVIE_GENRES_COMMAND = "delete from genrelinkmovie where idMovie = @movieId";

        private const string DELETE_MOVIE_FILES_COMMAND = "delete from bookmark where idFile in (select idFile from files where idMovie = @movieId); delete from resume where idFile in (select idFile from files where idMovie = @movieId); delete from duration where idFile in (select idFile from files where idMovie = @movieId); delete from files where idMovie = @movieId";

        private const string INSERT_MOVIE_ACTOR_COMMAND = "insert into actorlinkmovie(idMovie, idActor) values (@movieId, @actorId)";

        private const string INSERT_MOVIE_GENRE_COMMAND = "insert into genrelinkmovie(idMovie, idGenre) values (@movieId, @genreId)";

        private const string INSERT_ACTOR_COMMAND = "insert into actors(idActor, strActor) values (NULL, @name); select last_insert_rowid() as [id]";

        private const string INSERT_GENRE_COMMAND = "insert into genre(idGenre, strGenre) values (NULL, @name); select last_insert_rowid() as [id]";

        private const string INSERT_FILE_COMMAND = "insert into files(idFile, idPath, idMovie, strFilename) values (NULL, @pathId, @movieId, @fileName); select last_insert_rowid() as [id]";

        private const string INSERT_PATH_COMMAND = "insert into path(idPath, strPath, cdlabel) values (NULL, @path, ''); select last_insert_rowid() as [id]";

        private const string FIND_ACTOR_BY_NAME_COMMAND = "select idActor from actors where strActor = @name";

        private const string FIND_GENRE_BY_NAME_COMMAND = "select idGenre from genre where strGenre = @name";

        private const string FIND_PATH_COMMAND = "select idPath from path where strPath = @path";

        private const string DELETE_OLD_MOVIE_FILES_COMMAND = "delete from files where idFile in ({0}); delete from bookmark where idFile in ({0}); delete from resume where idFile in ({0}); delete from duration where idFile in ({0})";

        private const string FIND_OLD_MOVIE_FILES_COMMAND = "select idFile from files where idMovie = @movieId and idFile not in ({0}) order by idFile";

        private const int MAXIMUM_SHARES = 128;

        private SQLiteConnection connection = null;

        public MovieService()
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("Data Source=");
                sb.Append(Config.GetFile(Config.Dir.Database, @"VideoDatabaseV5.db3"));
                sb.Append(";");

                connection = new SQLiteConnection(sb.ToString());
                connection.Open();
            }
            catch (System.Exception ex)
            {
                log.Error(string.Format("Error connecting database, message: {0}", ex.Message), ex);
            }
        }

        public bool Check()
        {
            return true;
        }

        public ArrayList FindAllMoviesShortInfo()
        {
            ArrayList movies = new ArrayList();
            using (SQLiteCommand cmd = new SQLiteCommand(MOVIE_SHORT_INFO_COMMAND, connection))
            {
                using (SQLiteDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            MovieShortInfo movieShortInfo = new MovieShortInfo();
                            if (reader["idMovie"] != null)
                            {
                                movieShortInfo.Id = long.Parse(reader["idMovie"].ToString());
                            }
                            movieShortInfo.Name = reader["strTitle"].ToString();

                            movies.Add(movieShortInfo);
                        }
                    }
                }
            }
            return movies;
        }

        public MovieInfo FindMovieInfoById(long movieId)
        {
            ArrayList movieInfos = FindMovieInfoByIds(new ArrayList(new long[] { movieId }));
            if (movieInfos != null && movieInfos.Count > 0)
            {
                IEnumerator enumerator = movieInfos.GetEnumerator();
                if (enumerator.MoveNext())
                {
                    return enumerator.Current as MovieInfo;
                }
            }
            return null;
        }

        public ArrayList FindMovieInfoByIds(ArrayList ids)
        {
            ArrayList movieInfos = new ArrayList();

            string commandString;
            if (ids != null && ids.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (long id in ids)
                {
                    if (sb.Length > 0)
                    {
                        sb.Append(", ");
                    }
                    sb.Append(id);
                }

                commandString = string.Format(MOVIE_INFO_COMMAND, sb.ToString());
            }
            else
            {
                commandString = ALL_MOVIE_INFO_COMMAND;
            }

            using (SQLiteCommand command = new SQLiteCommand(commandString, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            MovieInfo movieInfo = new MovieInfo();
                            movieInfo.Id = long.Parse(reader["idMovie"].ToString());
                            movieInfo.Name = reader["strTitle"].ToString();
                            movieInfo.Tagline = reader["strTagLine"].ToString();
                            movieInfo.Description = reader["strPlot"].ToString();
                            movieInfo.IMDBRating = reader["fRating"].ToString();
                            movieInfo.IMDBNumber = reader["IMDBID"].ToString();
                            movieInfo.IMDBVotes = reader["strVotes"].ToString();
                            movieInfo.Year = reader["iYear"].ToString();
                            movieInfo.Runtime = reader["runtime"].ToString();
                            movieInfo.MPAARating = reader["mpaa"].ToString();
                            movieInfo.Director = reader["strDirector"].ToString();
                            movieInfo.Writer = reader["strCredits"].ToString();

                            movieInfo.IsWatched = false;
                            object obj = reader["iswatched"];
                            if (obj != null && !string.IsNullOrEmpty(obj.ToString()))
                            {
                                int isWatched;
                                if (int.TryParse(obj.ToString(), out isWatched))
                                {
                                    movieInfo.IsWatched = isWatched == 1;
                                }
                            }

                            string fileName = MediaPortal.Util.Utils.GetLargeCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);
                            if (File.Exists(fileName))
                            {
                                movieInfo.Picture = Convert.ToBase64String(File.ReadAllBytes(fileName));
                            }

                            fileName = MediaPortal.Util.Utils.GetCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);
                            if (File.Exists(fileName))
                            {
                                movieInfo.Thumb = Convert.ToBase64String(File.ReadAllBytes(fileName));
                            }

                            long movieId = movieInfo.Id;
                            using (SQLiteCommand actorsCommand = new SQLiteCommand(MOVIE_ACTORS_COMMAND, connection))
                            {
                                actorsCommand.Parameters.AddWithValue("@movieId", movieId);
                                using (SQLiteDataReader actorsReader = actorsCommand.ExecuteReader())
                                {
                                    if (actorsReader.HasRows)
                                    {
                                        while (actorsReader.Read())
                                        {
                                            Actor actor = new Actor();
                                            actor.Id = long.Parse(actorsReader["idActor"].ToString());
                                            actor.Name = actorsReader["strActor"].ToString();

                                            movieInfo.AddActor(actor);
                                        }
                                    }
                                }
                            }
                            using (SQLiteCommand genresCommand = new SQLiteCommand(MOVIE_GENRES_COMMAND, connection))
                            {
                                genresCommand.Parameters.AddWithValue("@movieId", movieId);
                                using (SQLiteDataReader genresReader = genresCommand.ExecuteReader())
                                {
                                    if (genresReader.HasRows)
                                    {
                                        while (genresReader.Read())
                                        {
                                            Genre genre = new Genre();
                                            genre.Id = long.Parse(genresReader["idGenre"].ToString());
                                            genre.Name = genresReader["strGenre"].ToString();

                                            movieInfo.AddGenre(genre);
                                        }
                                    }
                                }
                            }
                            using (SQLiteCommand filesCommand = new SQLiteCommand(MOVIE_FILES_COMMAND, connection))
                            {
                                filesCommand.Parameters.AddWithValue("@movieId", movieId);
                                using (SQLiteDataReader filesReader = filesCommand.ExecuteReader())
                                {
                                    if (filesReader.HasRows)
                                    {
                                        while (filesReader.Read())
                                        {
                                            ShareFile shareFile = new ShareFile();
                                            shareFile.Id = long.Parse(filesReader["idFile"].ToString());
                                            shareFile.Name = filesReader["strFilename"].ToString();
                                            shareFile.Path = filesReader["strPath"].ToString();

                                            movieInfo.AddFile(shareFile);
                                        }
                                    }
                                }
                            }
                            movieInfos.Add(movieInfo);
                        }
                    }
                }
            }
            return movieInfos;
        }

        public void UpdateMovieInfo(MovieInfo movieInfo, bool updatePicture)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {

                        // Actors
                        command.CommandText = DELETE_MOVIE_ACTORS_COMMAND;
                        command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                        command.ExecuteNonQuery();

                        foreach (Actor actor in movieInfo.Actors)
                        {
                            if (actor.Id == 0)
                            {
                                command.CommandText = INSERT_ACTOR_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@name", actor.Name);
                                actor.Id = long.Parse(command.ExecuteScalar().ToString());
                            }
                            command.CommandText = INSERT_MOVIE_ACTOR_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                            command.Parameters.AddWithValue("@actorId", actor.Id);
                            command.ExecuteNonQuery();
                        }

                        // Genres
                        command.CommandText = DELETE_MOVIE_GENRES_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                        command.ExecuteNonQuery();

                        foreach (Genre genre in movieInfo.Genres)
                        {
                            if (genre.Id == 0)
                            {
                                command.CommandText = INSERT_GENRE_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@name", genre.Name);
                                genre.Id = long.Parse(command.ExecuteScalar().ToString());
                            }
                            command.CommandText = INSERT_MOVIE_GENRE_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                            command.Parameters.AddWithValue("@genreId", genre.Id);
                            command.ExecuteNonQuery();
                        }

                        // Files
                        StringBuilder sb = new StringBuilder();
                        if (movieInfo.Files != null && movieInfo.Files.Count > 0)
                        {
                            foreach (ShareFile file in movieInfo.Files)
                            {
                                if (file.Id != 0)
                                {
                                    if (sb.Length > 0)
                                    {
                                        sb.Append(", ");
                                    }
                                    sb.Append(file.Id);
                                }
                            }
                        }

                        if (sb.Length == 0)
                        {
                            sb.Append("0");
                        }

                        command.CommandText = string.Format(FIND_OLD_MOVIE_FILES_COMMAND, sb.ToString());
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                        sb = new StringBuilder();
                        using (SQLiteDataReader filesReader = command.ExecuteReader())
                        {
                            if (filesReader.HasRows)
                            {
                                while (filesReader.Read())
                                {
                                    if (sb.Length > 0)
                                    {
                                        sb.Append(", ");
                                    }
                                    sb.Append(filesReader["idFile"].ToString());
                                }
                            }
                            else
                            {
                                sb.Append("0");
                            }
                        }

                        command.CommandText = string.Format(DELETE_OLD_MOVIE_FILES_COMMAND, sb.ToString());
                        command.Parameters.Clear();
                        command.ExecuteNonQuery();

                        foreach (ShareFile file in movieInfo.Files)
                        {
                            if (file.Id == 0)
                            {
                                long pathId = 0;
                                command.CommandText = FIND_PATH_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@path", file.Path);
                                object pathObj = command.ExecuteScalar();
                                if (pathObj == null)
                                {
                                    command.CommandText = INSERT_PATH_COMMAND;
                                    command.Parameters.Clear();
                                    command.Parameters.AddWithValue("@path", file.Path);
                                    pathId = long.Parse(command.ExecuteScalar().ToString());
                                }
                                else
                                {
                                    pathId = long.Parse(pathObj.ToString());
                                }

                                command.CommandText = INSERT_FILE_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@pathId", pathId);
                                command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                                command.Parameters.AddWithValue("@fileName", file.Name);
                                command.ExecuteNonQuery();
                            }
                        }

                        // Director
                        long directorId = 0;
                        command.CommandText = FIND_ACTOR_BY_NAME_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@name", movieInfo.Director);
                        object obj = command.ExecuteScalar();
                        if (obj == null)
                        {
                            command.CommandText = INSERT_ACTOR_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@name", movieInfo.Director);
                            directorId = long.Parse(command.ExecuteScalar().ToString());
                        }
                        else
                        {
                            directorId = long.Parse(obj.ToString());
                        }

                        // Common
                        command.CommandText = UPDATE_MOVIE_INFO_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@movieId", movieInfo.Id);
                        command.Parameters.AddWithValue("@title", movieInfo.Name);
                        command.Parameters.AddWithValue("@tagline", movieInfo.Tagline);
                        command.Parameters.AddWithValue("@directorId", directorId);
                        command.Parameters.AddWithValue("@writer", movieInfo.Writer);
                        command.Parameters.AddWithValue("@year", movieInfo.Year);
                        command.Parameters.AddWithValue("@runtime", movieInfo.Runtime);
                        command.Parameters.AddWithValue("@description", movieInfo.Description);
                        command.Parameters.AddWithValue("@imdbRating", movieInfo.IMDBRating);
                        command.Parameters.AddWithValue("@imdbNumber", movieInfo.IMDBNumber);
                        command.Parameters.AddWithValue("@imdbVotes", movieInfo.IMDBVotes);
                        command.Parameters.AddWithValue("@mpaaRating", movieInfo.MPAARating);
                        command.Parameters.AddWithValue("@isWatched", movieInfo.IsWatched ? 1 : 0);
                        command.Parameters.AddWithValue("@genres", movieInfo.GenresString);
                        command.Parameters.AddWithValue("@actors", movieInfo.ActorsString);

                        command.ExecuteNonQuery();

                        // Picture
                        if (updatePicture)
                        {
                            MediaPortal.Util.Utils.FileDelete(MediaPortal.Util.Utils.GetLargeCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name));
                            MediaPortal.Util.Utils.FileDelete(MediaPortal.Util.Utils.GetCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name));

                            if (movieInfo.Picture != null && movieInfo.Picture != string.Empty)
                            {
                                string largeCoverArtName = MediaPortal.Util.Utils.GetLargeCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);
                                string coverArtName = MediaPortal.Util.Utils.GetCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);

                                string tempFileName = Path.GetTempFileName();
                                File.WriteAllBytes(tempFileName, Convert.FromBase64String(movieInfo.Picture));

                                if (File.Exists(tempFileName))
                                {
                                    if (MediaPortal.Util.Picture.CreateThumbnail(tempFileName, largeCoverArtName, (int)MediaPortal.Util.Thumbs.ThumbLargeResolution, (int)MediaPortal.Util.Thumbs.ThumbLargeResolution, 0, MediaPortal.Util.Thumbs.SpeedThumbsSmall))
                                        MediaPortal.Util.Picture.CreateThumbnail(tempFileName, coverArtName, (int)MediaPortal.Util.Thumbs.ThumbResolution, (int)MediaPortal.Util.Thumbs.ThumbResolution, 0, MediaPortal.Util.Thumbs.SpeedThumbsLarge);
                                }
                                File.Delete(tempFileName);
                            }
                        }
                    }
                    transaction.Commit();
                }
                catch (System.Exception ex)
                {
                    transaction.Rollback();
                    log.Error("Error updating movie info", ex);
                    throw new MPMovieCatalogException(string.Format("Error updating movie info: {0}", ex.Message));
                }
            }
        }

        public ArrayList FindAvailableGenres(long movieId)
        {
            ArrayList genres = new ArrayList();
            using (SQLiteCommand command = new SQLiteCommand(AVAIL_GENRES_COMMAND, connection))
            {
                command.Parameters.AddWithValue("@movieId", movieId);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Genre genre = new Genre();
                            genre.Id = long.Parse(reader["idGenre"].ToString());
                            genre.Name = reader["strGenre"].ToString();

                            genres.Add(genre);
                        }
                    }
                }
            }
            return genres;
        }

        public ArrayList FindAvailableActors(long movieId)
        {
            ArrayList actors = new ArrayList();
            using (SQLiteCommand command = new SQLiteCommand(AVAIL_ACTORS_COMMAND, connection))
            {
                command.Parameters.AddWithValue("@movieId", movieId);
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Actor actor = new Actor();
                            actor.Id = long.Parse(reader["idActor"].ToString());
                            actor.Name = reader["strActor"].ToString();

                            actors.Add(actor);
                        }
                    }
                }
            }
            return actors;
        }

        public long InsertMovieInfo(MovieInfo movieInfo)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    long movieId = 0;
                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {
                        // Movie
                        command.CommandText = INSERT_MOVIE_COMMAND;
                        command.Parameters.AddWithValue("@pathId", 1);
                        command.Parameters.AddWithValue("@hasSubtitles", 0);
                        command.Parameters.AddWithValue("@discId", "");

                        movieId = long.Parse(command.ExecuteScalar().ToString());

                        // Director
                        long directorId = 0;
                        command.CommandText = FIND_ACTOR_BY_NAME_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@name", movieInfo.Director);
                        object obj = command.ExecuteScalar();
                        if (obj == null)
                        {
                            command.CommandText = INSERT_ACTOR_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@name", movieInfo.Director);
                            directorId = long.Parse(command.ExecuteScalar().ToString());
                        }
                        else
                        {
                            directorId = long.Parse(obj.ToString());
                        }

                        // MovieInfo
                        command.CommandText = INSERT_MOVIE_INFO_COMMAND;
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@movieId", movieId);
                        command.Parameters.AddWithValue("@title", movieInfo.Name);
                        command.Parameters.AddWithValue("@tagline", movieInfo.Tagline);
                        command.Parameters.AddWithValue("@directorId", directorId);
                        command.Parameters.AddWithValue("@writer", movieInfo.Writer);
                        command.Parameters.AddWithValue("@year", movieInfo.Year);
                        command.Parameters.AddWithValue("@runtime", movieInfo.Runtime);
                        command.Parameters.AddWithValue("@description", movieInfo.Description);
                        command.Parameters.AddWithValue("@imdbRating", movieInfo.IMDBRating);
                        command.Parameters.AddWithValue("@imdbNumber", movieInfo.IMDBNumber);
                        command.Parameters.AddWithValue("@imdbVotes", movieInfo.IMDBVotes);
                        command.Parameters.AddWithValue("@mpaaRating", movieInfo.MPAARating);
                        command.Parameters.AddWithValue("@isWatched", movieInfo.IsWatched ? 1 : 0);
                        command.Parameters.AddWithValue("@genres", movieInfo.GenresString);
                        command.Parameters.AddWithValue("@actors", movieInfo.ActorsString);

                        command.ExecuteNonQuery();

                        // Actors
                        foreach (Actor actor in movieInfo.Actors)
                        {
                            if (actor.Id == 0)
                            {
                                command.CommandText = INSERT_ACTOR_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@name", actor.Name);
                                actor.Id = long.Parse(command.ExecuteScalar().ToString());
                            }
                            command.CommandText = INSERT_MOVIE_ACTOR_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@movieId", movieId);
                            command.Parameters.AddWithValue("@actorId", actor.Id);
                            command.ExecuteNonQuery();
                        }

                        // Genres
                        foreach (Genre genre in movieInfo.Genres)
                        {
                            if (genre.Id == 0)
                            {
                                command.CommandText = INSERT_GENRE_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@name", genre.Name);
                                genre.Id = long.Parse(command.ExecuteScalar().ToString());
                            }
                            command.CommandText = INSERT_MOVIE_GENRE_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@movieId", movieId);
                            command.Parameters.AddWithValue("@genreId", genre.Id);
                            command.ExecuteNonQuery();
                        }

                        // Files
                        foreach (ShareFile file in movieInfo.Files)
                        {
                            long pathId = 0;
                            command.CommandText = FIND_PATH_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@path", file.Path);
                            object pathObj = command.ExecuteScalar();
                            if (pathObj == null)
                            {
                                command.CommandText = INSERT_PATH_COMMAND;
                                command.Parameters.Clear();
                                command.Parameters.AddWithValue("@path", file.Path);
                                pathId = long.Parse(command.ExecuteScalar().ToString());
                            }
                            else
                            {
                                pathId = long.Parse(pathObj.ToString());
                            }

                            command.CommandText = INSERT_FILE_COMMAND;
                            command.Parameters.Clear();
                            command.Parameters.AddWithValue("@pathId", pathId);
                            command.Parameters.AddWithValue("@movieId", movieId);
                            command.Parameters.AddWithValue("@fileName", file.Name);
                            command.ExecuteNonQuery();
                        }


                        // Picture
                        MediaPortal.Util.Utils.FileDelete(MediaPortal.Util.Utils.GetLargeCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name));
                        MediaPortal.Util.Utils.FileDelete(MediaPortal.Util.Utils.GetCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name));

                        if (movieInfo.Picture != null && movieInfo.Picture != string.Empty)
                        {
                            string largeCoverArtName = MediaPortal.Util.Utils.GetLargeCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);
                            string coverArtName = MediaPortal.Util.Utils.GetCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movieInfo.Name);

                            string tempFileName = Path.GetTempFileName();
                            File.WriteAllBytes(tempFileName, Convert.FromBase64String(movieInfo.Picture));

                            if (File.Exists(tempFileName))
                            {
                                if (MediaPortal.Util.Picture.CreateThumbnail(tempFileName, largeCoverArtName, (int)MediaPortal.Util.Thumbs.ThumbLargeResolution, (int)MediaPortal.Util.Thumbs.ThumbLargeResolution, 0, MediaPortal.Util.Thumbs.SpeedThumbsSmall))
                                    MediaPortal.Util.Picture.CreateThumbnail(tempFileName, coverArtName, (int)MediaPortal.Util.Thumbs.ThumbResolution, (int)MediaPortal.Util.Thumbs.ThumbResolution, 0, MediaPortal.Util.Thumbs.SpeedThumbsLarge);
                            }
                            File.Delete(tempFileName);
                        }
                    }
                    transaction.Commit();
                    return movieId;
                }
                catch (System.Exception ex)
                {
                    transaction.Rollback();
                    log.Error("Error inserting movie info", ex);
                    throw new MPMovieCatalogException(string.Format("Error inserting movie info: {0}", ex.Message));
                }
            }
        }

        public void DeleteMovieInfo(MovieShortInfo movie)
        {
            using (SQLiteTransaction transaction = connection.BeginTransaction())
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    command.CommandText = DELETE_MOVIE_ACTORS_COMMAND;
                    command.Parameters.AddWithValue("@movieId", movie.Id);
                    command.ExecuteNonQuery();

                    command.CommandText = DELETE_MOVIE_GENRES_COMMAND;
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@movieId", movie.Id);
                    command.ExecuteNonQuery();

                    command.CommandText = DELETE_MOVIE_FILES_COMMAND;
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@movieId", movie.Id);
                    command.ExecuteNonQuery();

                    command.CommandText = DELETE_MOVIE_INFO_COMMAND;
                    command.Parameters.Clear();
                    command.Parameters.AddWithValue("@movieId", movie.Id);
                    command.ExecuteNonQuery();

                    MediaPortal.Util.Utils.FileDelete(MediaPortal.Util.Utils.GetLargeCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movie.Name));
                    MediaPortal.Util.Utils.FileDelete(MediaPortal.Util.Utils.GetCoverArtName(MediaPortal.Util.Thumbs.MovieTitle, movie.Name));

                    transaction.Commit();
                }
            }
        }

        public ArrayList GetShares()
        {
            ArrayList shares = new ArrayList();
            using (MediaPortal.Profile.Settings xmlreader = new MediaPortal.Profile.Settings(Config.GetFile(Config.Dir.Config, "MediaPortal.xml")))
            {
                for (int index = 0; index < MAXIMUM_SHARES; index++)
                {
                    string shareName = String.Format("sharename{0}", index);
                    string sharePath = String.Format("sharepath{0}", index);

                    string shareNameData = xmlreader.GetValueAsString("movies", shareName, "");
                    string sharePathData = xmlreader.GetValueAsString("movies", sharePath, "");

                    if (shareNameData != null && shareNameData.Length > 0)
                    {
                        Share share = new Share(shareNameData, sharePathData);
                        shares.Add(share);
                        WalkDirectoryTree(share);
                    }
                }

            }

            return shares;
        }

        public ArrayList GetFiles(string path, string filter)
        {
            ArrayList shareFiles = new ArrayList();
            FileInfo[] files = null;

            DirectoryInfo root = new DirectoryInfo(path);
            try
            {
                files = root.GetFiles(filter);
            }
            catch (System.Exception ex)
            {
                log.Error(string.Format("Error getting file list for path: {0}, message: {1}", path, ex.Message), ex);
            }

            if (files != null)
            {
                foreach (FileInfo fileInfo in files)
                {
                    ShareFile shareFile = new ShareFile(fileInfo.Name, fileInfo.DirectoryName + @"\");
                    shareFiles.Add(shareFile);
                }
            }

            return shareFiles;
        }

        private void WalkDirectoryTree(Share shareRoot)
        {
            try
            {
                DirectoryInfo root = new DirectoryInfo(shareRoot.Path);
                DirectoryInfo[] subDirs = root.GetDirectories();
                foreach (DirectoryInfo dirInfo in subDirs)
                {
                    Share childShare = new Share(dirInfo.Name, dirInfo.FullName);
                    shareRoot.AddChild(childShare);
                    WalkDirectoryTree(childShare);
                }
            }
            catch (System.Exception ex)
            {
                log.Error(string.Format("Error getting tree for share: {0}, message: {1}", shareRoot.Path, ex.Message), ex);
            }

        }
    }
}
